<?php
	/**
	 * MusicNet Alignment Tool: MNItem
	 *
	 * Copyright (c) 2010 Joe Lambert
	 * 
	 * Permission is hereby granted, free of charge, to any person obtaining
	 * a copy of this software and associated documentation files (the
	 * "Software"), to deal in the Software without restriction, including
	 * without limitation the rights to use, copy, modify, merge, publish,
	 * distribute, sublicense, and/or sell copies of the Software, and to
	 * permit persons to whom the Software is furnished to do so, subject to
	 * the following conditions:
	 * 
	 * The above copyright notice and this permission notice shall be
	 * included in all copies or substantial portions of the Software.
	 * 
	 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
	 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
	 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
	 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
	 * LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
	 * OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
	 * WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
	 *  
	 * @author Joe Lambert
	 * @version 1.0
	 * @copyright Joe Lambert, 23 August, 2010
	 **/
	
	require_once('config.php');
	
	class Database
	{
		private static $instance; // Singleton variable
		private $db_link;
		
		// Query Builder variables
		private $select = array();
		private $from = array();
		private $leftjoin = array();
		private $where = array();
		private $orderby = array();
		private $limit = null;
		
		// Query Builder functions
		private function reset()
		{
			$this->select = array();
			$this->from = array();
			$this->leftjoin = array();
			$this->where = array();
			$this->orderby = array();
			$this->limit = null;
		}
		
		public function select($fields)
		{
			if(is_string($fields))
			{
				if($fields != '*')
					$this->select[] = $fields;
			}
			else if(is_array($fields))
			{
				$this->select = array_merge($this->select, $fields);
			}
			
			return $this;
		}
		
		public function from($tables)
		{
			if(is_string($tables))
			{
				$this->from[] = $tables;
			}
			else if(is_array($fields))
			{
				$this->from = array_merge($this->from, $tables);
			}
			
			return $this;
		}
		
		public function leftjoin($table, $on)
		{
			if(is_string($table) && is_array($on))
			{
				$this->leftjoin[$table] = $on;
			}
			
			return $this;
		}
		
		public function where($field, $value=null)
		{
			if(!$value && is_array($field))
			{
				foreach($field as $k=>$v)
				{
					$this->where[$k] = $v;
				}
			}
			else
				$this->where[$field] = $value;
				
			return $this;
		}
		
		public function in($field, $array)
		{
			if(is_string($field) && is_array($array))
			{
				$this->where["$field IN"] = $array;
			}
			
			return $this;
		}
		
		public function orderby($field, $direction='ASC')
		{
			if(!$direction && is_array($field))
			{
				foreach($field as $k=>$v)
				{
					$v = strtoupper($v);
					if(!in_array($v, array('ASC', 'DESC')))
						$v = 'ASC';
						
					$this->orderby[$k] = $v;
				}
			}
			else
			{
				$direction = strtoupper($direction);
				if(!in_array($direction, array('ASC', 'DESC')))
					$direction = 'ASC';
					
				$this->orderby[$field] = $direction;				
			}
				
			return $this;
		}
		
		public function limit($limit)
		{
			if(is_numeric($limit))
				$this->limit = $limit;
				
			return $this;
		}
		
		public function get()
		{
			if(count($this->from))
			{
				$query = "SELECT "; 
				if(count($this->select))
				{
					$firstloop = true;
					foreach($this->select as $k=>$v)
					{							
						if(!$firstloop)
							$query .= ", ";
							
						if(is_numeric($k))
						{
							if(strpos($v, '.') !== FALSE)
							{
								$parts = explode('.', $v);
								$query .= "`".mysql_real_escape_string($parts[0])."`.".mysql_real_escape_string($parts[1]);	
							}
							else
								$query .= "`".mysql_real_escape_string($v)."`";	
						}
						else
						{
							if(strpos($k, '.') !== FALSE)
							{
								$parts = explode('.', $k);
								$query .= "`".mysql_real_escape_string($parts[0])."`.".mysql_real_escape_string($parts[1])." AS ".mysql_real_escape_string($v);	
							}
							else
								$query .= "`".mysql_real_escape_string($k)."` AS ".mysql_real_escape_string($v);
						}
							
						
						$firstloop = false;
					}
				}
				else
					$query .= "*";
					
				$query .= " FROM ";
				
				$firstloop = true;
				foreach($this->from as $table)
				{
					if(!$firstloop)
						$query .= ", ";
						
					$query .= "`".mysql_real_escape_string($table)."`";
					
					$firstloop = false;
				}
				
				if(count($this->leftjoin))
				{
					$table_str = "";
					$conditions_str = "";
					
					$firstloop = true;
					foreach($this->leftjoin as $table=>$conditions)
					{
						if(!$firstloop)
						{
							$table_str .= ", ";
							$conditions_str .= ", ";
						}

						$table_str .= "`".mysql_real_escape_string($table)."`";
						$conditions_str .= mysql_real_escape_string($conditions[0])." = ".mysql_real_escape_string($conditions[1]);

						$firstloop = false;
					}
					
					$query .= " LEFT JOIN ($table_str) ON $conditions_str ";
				}
				
				if(count($this->where))
				{
					$query .= " WHERE ";
					
					$firstloop = true;
					foreach($this->where as $k=>$v)
					{
						if(!$firstloop)
							$query .= " AND ";
						
						// Work out the operator
						$parts = explode(' ', $k);
						
						if(count($parts) == 2)
						{
							$k = $parts[0];
							$operator = $parts[1];
							
							// Check if this is an IN
							if($operator == 'IN')
							{
								$s = "(";
								$i = 0;
								foreach($v as $in)
								{
									if($i++ > 0)
										$s .= ", ";
										
									if(!is_numeric($in))
										$s .= "'".mysql_real_escape_string($in)."'";
									else
										$s .= mysql_real_escape_string($in);
								}
								
								$s .= ")";
								$v = $s;
							}
							else if(!in_array($operator, array('=', '!=', '>', '<', '>=', '<=')))
								$operator = "=";
						}
						else
							$operator = "=";
							
						if($v == 'NULL')
							$operator = 'IS';
						
						$query .= "$k $operator ";
						
						if($operator == "IN")
							$query .= $v;
						else if(is_numeric($v) || $v == 'NULL')
							$query .= mysql_real_escape_string($v);
						else
							$query .= "'".mysql_real_escape_string($v)."'";

						$firstloop = false;
					}
				}
				
				if(count($this->orderby))
				{
					$query .= " ORDER BY ";
					
					$firstloop = true;
					foreach($this->orderby as $field=>$direction)
					{
						if(!$firstloop)
							$query .= ", ";
							
						$query .= mysql_real_escape_string($field)." ".mysql_real_escape_string($direction);						
						
						$firstloop = false;
					}
				}
				
				if($this->limit)
					$query .= " LIMIT ".$this->limit;
				
				//echo "$query<br/>";
				$result = mysql_query($query, $this->db_link);
			}
			else
				$result = false;
				
			$this->reset();
			return $result;
		}
		
		function query($query)
		{
			return mysql_query($query);
		}
		
		/**
		 * Constructor
		 *
		 * @return void
		 * @author Joe Lambert
		 **/
		private function __construct() 
	    {
	        // Connect to the database
			$this->db_link = mysql_connect('localhost', DB_USER, DB_PASS);
			if (!$this->db_link) {
			    die('Could not connect to server: ' . mysql_error());
			}
			
			$db = mysql_select_db(DB_NAME, $this->db_link);
			if(!$db)
				die('Could not connect to database: ' . mysql_error());
				
			$this->reset();
	    }
		
		/**
		 * Singleton instance method
		 *
		 * @static
		 * @return object
		 * @author Joe Lambert
		 **/
	    public static function instance() 
	    {
	        if (!isset(self::$instance)) {
	            $c = __CLASS__;
	            self::$instance = new $c;
	        }

	        return self::$instance;
	    }
	
		/**
		 * Closes the mySQL connection
		 *
		 * @return void
		 * @author Joe Lambert
		 */
		public function close()
		{
			mysql_close($this->db_link);
		}
	}
?>